﻿using OfficeOpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace excelread
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        string connectionString = @"data source=121.40.79.86;initial catalog=AnjiSys_RandamWl;user id=sa;password=anji2008";
        string formattedDate = "";//独立序号。
        SqlConnection connection = null;
        private void button1_Click(object sender, EventArgs e)
        {
            DateTime now = DateTime.Now;
            formattedDate = now.ToString("HHmmss");
            textBox1.Text = formattedDate;
            textBox2.Text = formattedDate;
            using (connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (OpenFileDialog folderBrowserDialog = new OpenFileDialog())
                {
                    if (folderBrowserDialog.ShowDialog() == DialogResult.OK)
                    {
                        string selectedFolderPath = folderBrowserDialog.FileName;
                        MessageBox.Show("Selected Folder: " + selectedFolderPath);
                        this.label1.Text = selectedFolderPath;
                        readexcel(selectedFolderPath);
                    }
                }

                connection.Close();
            }
        }

        private void readexcel(string filePath)
        {
            String time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            //string filePath = "example.xlsx"; // Excel 文件路径

            // 设置 EPPlus 的 LicenseContext
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

            // 打开 Excel 文件
            using (var package = new ExcelPackage(new FileInfo(filePath)))
            {
                // 获取第一个 Sheet
                var worksheet = package.Workbook.Worksheets[0];

                // 获取 Sheet 的总行数
                int rowCount = worksheet.Dimension.Rows;

                // 从第 3 行开始循环读取
                for (int row = 3; row <= rowCount; row++)
                {
                    // 读取第 2、3、4、5 列的数据
                    string col2 = worksheet.Cells[row, 2].Text;
                    string col3 = worksheet.Cells[row, 3].Text;
                    string col4 = worksheet.Cells[row, 4].Text;
                    string col5 = worksheet.Cells[row, 5].Text;

                    //// 如果某一列数据为空，跳过该行
                    //if (string.IsNullOrEmpty(col2) && string.IsNullOrEmpty(col3) &&
                    //    string.IsNullOrEmpty(col4) && string.IsNullOrEmpty(col5))
                    //{
                    //    continue;
                    //}
                    // 打印到控制台
                    Console.WriteLine($"行 {row}: 列2={col2}, 列3={col3}, 列4={col4}, 列5={col5}");
                    try
                    {
                        int type = gettype(col2);
                        if (col3.Length > 0 && col4.Length > 0)
                        {
                           

                            int start = int.Parse(col3.Split('/')[0]);
                            int end = int.Parse(col4.Split('/')[0]);
                            string code = "";
                            for (int i = start; i <= end; i++) {
                                code = i.ToString().PadLeft(3, '0') + "/" + col3.Split('/')[1] + "/" + col3.Split('/')[2];
                                Console.WriteLine($"序号：" + code);
                                string sql = String.Format(" insert into jiu(code, type, pici, tdate)  VALUES('{0}', {1}, {2}, '{3}')", code, type, formattedDate, time);
                                int s = skydream.SqlHelp.insertRCmd(connection, sql);
                            }
                        }

                        if (col5.Length > 0 )
                        {
                            //int type = 5;//箱标数据
                            string code = col5;
                            string sql = String.Format(" insert into jiu(code, type, pici, tdate)  VALUES('{0}', {1}, {2}, '{3}')", code, type, formattedDate, time);
                            int s = skydream.SqlHelp.insertRCmd(connection, sql);
                        }
                            
                    }
                    catch (Exception ee) {
                        MessageBox.Show(ee.Message);
                    }

                    MessageBox.Show("导入完成！");
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            String outfile = textBox2.Text + ".txt";
            ArrayList list = new ArrayList();
            string sql = String.Format(" select name,code,type from ckwnum b right join jiu a  on a.id=b.id  where b.id in(select id from jiu where pici='{0}') order by a.id ", textBox2.Text);
            using (connection = new SqlConnection(connectionString))
            {
                DataSet ds = skydream.SqlHelp.getBasicTable(connection, sql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string name = ds.Tables[0].Rows[i][0].ToString();
                    string code = ds.Tables[0].Rows[i][1].ToString();
                    int stype = int.Parse(ds.Tables[0].Rows[i][2].ToString());
                    string type = gettype(stype);
                    string str = "";
                    if (stype == 1)
                    {
                        str = String.Format("http://anjismart.com/a2013.html?fwm={0}\t{1}\t{2}", name, code, type);
                    }
                    else if (stype == 2)
                    {
                        str = String.Format("http://anjismart.com/adrogon.html?fwm={0}\t{1}\t{2}", name, code, type);
                    }
                    else if (stype == 3)
                    {
                        str = String.Format("http://anjismart.com/az15.html?fwm={0}\t{1}\t{2}", name, code, type);
                    }
                    else if (stype == 4)
                    {
                        str = String.Format("http://anjismart.com/az30.html?fwm={0}\t{1}\t{2}", name, code, type);
                    }
                    //else if (stype == 5)
                    //{
                    //    str = String.Format("http://anjismart.com/adrogon.html?fwm={0}\t{1}\t{2}", name, code, type);
                    //}

                    list.Add(str);
                }
            }
            WriteArrayListToFile(list, outfile);
            MessageBox.Show("导出完成。");
        }

      public  void WriteArrayListToFile(ArrayList list, string filePath)
        {
            // 使用 StreamWriter 写入文件
            using (StreamWriter writer = new StreamWriter(filePath))
            {
                // 遍历 ArrayList 中的每一项
                foreach (var item in list)
                {
                    // 将每一项写入文件
                    writer.WriteLine(item);
                }
            }
        }

        private int gettype(string col2)
        {
            int type = 1;
            if (col2.Contains("珍酒2013真实年份酒标"))
            {
                type = 1;
            }
            else if (col2.Contains("珍酒2013真实年份甲辰龙年"))
            {
                type = 2;
            }
            else if (col2.Contains("珍酒珍十五金奖"))
            {
                type = 3;
            }
            else if (col2.Contains("珍酒珍三十金奖"))
            {
                type = 4;
            }
            return type;
        }
        private string gettype(int type)
        {
            String str = "";
            if (type == 1)
            {
                str = "珍酒2013真实年份酒标";
            }
            else if (type == 2)
            {
                str = "珍酒2013真实年份甲辰龙年";
            }
            else if (type == 3)
            {
                str = "珍酒珍十五金奖";
            }
            else if (type == 4)
            {
                str = "珍酒珍三十金奖";
            }
            else if (type == 5)
            {
                str = "箱标";
            }
            return str;
        }

        private void button3_Click(object sender, EventArgs e)
        {

            StringBuilder sb = new StringBuilder();
            string sql = String.Format("  select distinct pici,tdate from jiu where pici is not null ", textBox2.Text);
            using (connection = new SqlConnection(connectionString))
            {
                DataSet ds = skydream.SqlHelp.getBasicTable(connection, sql);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string name = ds.Tables[0].Rows[i][0].ToString();
                    string code = ds.Tables[0].Rows[i][1].ToString();

                    sb.Append(name + " " + code + "\r\n");
                }
            }
            textBox3.Text = sb.ToString();
        }
    }
}
